import frappe
from frappe import _
from frappe.utils import flt, getdate, add_months, nowdate
from erpnext.accounts.report.financial_statements import (
    filter_accounts,
    set_gl_entries_by_account,
    filter_out_zero_value_rows
)

value_fields = (
    "opening_debit", "opening_credit",
    "debit", "credit",
    "closing_debit", "closing_credit"
)

def execute(filters=None):
    filters = frappe._dict(filters or {})
    validate_filters(filters)
    add_period_dates(filters)
    columns = get_columns(filters)
    data = get_data(filters)
    return columns, data

def validate_filters(filters):
    """验证筛选条件的有效性"""
    if not filters.company:
        frappe.throw(_("公司为必填项"))
    if not filters.fiscal_year:
        frappe.throw(_("会计年度为必填项"))
    
    # 获取会计年度起止日期
    fiscal_year = frappe.get_cached_value(
        "Fiscal Year", filters.fiscal_year, 
        ["year_start_date", "year_end_date"], as_dict=True
    )
    if not fiscal_year:
        frappe.throw(_("会计年度 {0} 不存在").format(filters.fiscal_year))
    
    filters.year_start_date = getdate(fiscal_year.year_start_date)
    filters.year_end_date = getdate(fiscal_year.year_end_date)
    
    # 确保月份筛选条件存在
    if not filters.get("from_month"):
        filters.from_month = "一月"
    if not filters.get("to_month"):
        filters.to_month = "十二月"

def add_period_dates(filters):
    """计算期间起止日期"""
    month_map = {
        "一月": 1, "二月": 2, "三月": 3, "四月": 4,
        "五月": 5, "六月": 6, "七月": 7, "八月": 8,
        "九月": 9, "十月": 10, "十一月": 11, "十二月": 12
    }
    
    # 提取会计年度年份
    year = filters.year_start_date.year
    
    # 转换月份为数字
    from_month = month_map[filters.from_month]
    to_month = month_map[filters.to_month]
    
    # 计算期间开始日期（当月第一天）
    filters.period_start_date = getdate(f"{year}-{from_month:02d}-01")
    
    # 计算期间结束日期（当月最后一天）
    if to_month == 12:
        next_month = 1
        next_year = year + 1
    else:
        next_month = to_month + 1
        next_year = year
    
    next_month_first_day = getdate(f"{next_year}-{next_month:02d}-01")
    filters.period_end_date = add_months(next_month_first_day, -1)

def get_columns(filters):
    """定义报表列结构"""
    company_currency = frappe.get_cached_value("Company", filters.company, "default_currency")
    
    return [
        {
            "fieldname": "account",
            "label": _("会计科目"),
            "fieldtype": "Link",
            "options": "Account",
            "width": 300
        },
        {
            "fieldname": "account_name",
            "label": _("科目名称"),
            "fieldtype": "Data",
            "width": 200
        },
        {
            "fieldname": "opening_debit",
            "label": _("期初借方"),
            "fieldtype": "Currency",
            "options": company_currency,
            "width": 120
        },
        {
            "fieldname": "opening_credit",
            "label": _("期初贷方"),
            "fieldtype": "Currency",
            "options": company_currency,
            "width": 120
        },
        {
            "fieldname": "debit",
            "label": _("本期借方"),
            "fieldtype": "Currency",
            "options": company_currency,
            "width": 120
        },
        {
            "fieldname": "credit",
            "label": _("本期贷方"),
            "fieldtype": "Currency",
            "options": company_currency,
            "width": 120
        },
        {
            "fieldname": "closing_debit",
            "label": _("期末借方"),
            "fieldtype": "Currency",
            "options": company_currency,
            "width": 120
        },
        {
            "fieldname": "closing_credit",
            "label": _("期末贷方"),
            "fieldtype": "Currency",
            "options": company_currency,
            "width": 120
        }
    ]

def get_data(filters):
    """获取并处理报表数据"""
    # 关键修复：先获取科目数据并赋值给accounts变量
    accounts = get_accounts(filters)
    if not accounts:
        return []
    
    # 计算科目层级（indent）
    accounts = calculate_account_indent(accounts)
    
    # 构建科目映射关系
    accounts_by_name = {acc.name: acc for acc in accounts}
    parent_children_map = {}
    for acc in accounts:
        parent = acc.parent_account or ""
        parent_children_map.setdefault(parent, []).append(acc.name)
    
    # 初始化总账数据存储字典
    gl_entries_by_account = {}
    
    # 获取总账分录数据
    set_gl_entries_by_account(
        filters.company,
        filters.period_start_date,
        filters.period_end_date,
        filters,
        gl_entries_by_account,
        ignore_closing_entries=True
    )
    
    # 计算期初余额
    opening_balances = get_opening_balances(filters, accounts_by_name)
    
    # 计算每个科目的余额
    calculate_values(accounts, gl_entries_by_account, opening_balances, filters)
    
    # 汇总子科目到父科目
    accumulate_values_into_parents(accounts, accounts_by_name)
    
    # 准备报表展示数据（此时accounts已定义）
    data = prepare_data(accounts, parent_children_map, filters)
    
    # 过滤零值行（根据用户选择）
    show_zero = filters.get("show_zero_values", 0)
    if not show_zero:
        filtered_data = []
        account_names = [row["account"] for row in data]
        
        for row in data:
            # 检查是否有有效值
            has_value = any([
                flt(row["opening_debit"]) != 0,
                flt(row["opening_credit"]) != 0,
                flt(row["debit"]) != 0,
                flt(row["credit"]) != 0,
                flt(row["closing_debit"]) != 0,
                flt(row["closing_credit"]) != 0
            ])
            
            # 组科目需检查是否有子科目有值
            if row["is_group"]:
                has_child_with_value = False
                for child in parent_children_map.get(row["account"], []):
                    if child in account_names:
                        child_row = next((r for r in data if r["account"] == child), None)
                        if child_row and child_row["has_value"]:
                            has_child_with_value = True
                            break
                if has_value or has_child_with_value:
                    filtered_data.append(row)
            else:
                if has_value:
                    filtered_data.append(row)
        
        data = filtered_data
    
    return data

def get_accounts(filters):
    """获取指定公司/父科目的所有科目（含多级子科目）"""
    accounts = frappe.db.sql("""
        select name, account_number, parent_account, account_name, is_group, lft, rgt
        from `tabAccount` 
        where company = %s 
        order by lft
    """, filters.company, as_dict=True)
    
    if filters.get("parent_account"):
        try:
            parent = frappe.get_doc("Account", filters.parent_account)
            
            # 双重验证：lft/rgt范围 + 递归子科目
            accounts_by_lft_rgt = [a for a in accounts if a.lft >= parent.lft and a.rgt <= parent.rgt]
            child_accounts = [parent.name]
            
            def get_children(parent_name):
                children = [a.name for a in accounts if a.parent_account == parent_name]
                for child in children:
                    child_accounts.append(child)
                    get_children(child)
            
            get_children(parent.name)
            accounts = [a for a in accounts_by_lft_rgt if a.name in child_accounts]
            
        except Exception as e:
            frappe.log_error(f"筛选父科目失败: {str(e)}", "Disclosure Details CN")
            return []
    
    return accounts

def calculate_account_indent(accounts):
    """计算科目层级（indent）"""
    parent_children_map = {}
    account_map = {acc.name: acc for acc in accounts}
    
    # 构建父子关系映射
    for acc in accounts:
        parent = acc.parent_account or ""
        parent_children_map.setdefault(parent, []).append(acc.name)
    
    # 递归设置层级
    def set_indent(account_name, current_indent):
        acc = account_map.get(account_name)
        if acc:
            acc.indent = current_indent
            for child_name in parent_children_map.get(account_name, []):
                set_indent(child_name, current_indent + 1)
    
    # 从根科目开始
    root_accounts = parent_children_map.get("", [])
    for root in root_accounts:
        set_indent(root, 0)
    
    return accounts

def get_opening_balances(filters, accounts_by_name):
    """获取科目期初余额"""
    opening_balances = {
        name: frappe._dict({"opening_debit": 0.0, "opening_credit": 0.0})
        for name in accounts_by_name
    }
    
    # 计算会计年度内的期初余额
    if filters.period_start_date > filters.year_start_date:
        result = frappe.db.sql("""
            select account, sum(debit) as debit, sum(credit) as credit
            from `tabGL Entry`
            where company = %s
                and posting_date >= %s
                and posting_date < %s
                and is_cancelled = 0
            group by account
        """, (filters.company, filters.year_start_date, filters.period_start_date), as_dict=True)
        
        for d in result:
            if d.account in opening_balances:
                opening_balances[d.account].opening_debit = flt(d.debit)
                opening_balances[d.account].opening_credit = flt(d.credit)
    
    return opening_balances

def calculate_values(accounts, gl_entries_by_account, opening_balances, filters):
    """计算每个科目的余额"""
    for acc in accounts:
        # 期初余额
        ob = opening_balances.get(acc.name, {})
        acc.opening_debit = flt(ob.get("opening_debit", 0.0))
        acc.opening_credit = flt(ob.get("opening_credit", 0.0))
        
        # 本期发生额
        acc.debit = 0.0
        acc.credit = 0.0
        for entry in gl_entries_by_account.get(acc.name, []):
            acc.debit += flt(entry.debit)
            acc.credit += flt(entry.credit)
        
        # 期末余额
        acc.closing_debit = acc.opening_debit + acc.debit
        acc.closing_credit = acc.opening_credit + acc.credit

def accumulate_values_into_parents(accounts, accounts_by_name):
    """汇总子科目到父科目"""
    for acc in reversed(accounts):
        if acc.parent_account and acc.parent_account in accounts_by_name:
            parent = accounts_by_name[acc.parent_account]
            for field in value_fields:
                parent[field] += acc[field]

def prepare_data(accounts, parent_children_map, filters):
    """准备报表展示数据"""
    data = []
    for acc in accounts:
        # 标记是否有值
        has_value = any([
            flt(acc.opening_debit) != 0,
            flt(acc.opening_credit) != 0,
            flt(acc.debit) != 0,
            flt(acc.credit) != 0,
            flt(acc.closing_debit) != 0,
            flt(acc.closing_credit) != 0
        ])
        
        row = {
            "account": acc.name,
            "account_name": acc.account_name,
            "parent_account": acc.parent_account,
            "indent": acc.indent,
            "opening_debit": flt(acc.opening_debit, 2),
            "opening_credit": flt(acc.opening_credit, 2),
            "debit": flt(acc.debit, 2),
            "credit": flt(acc.credit, 2),
            "closing_debit": flt(acc.closing_debit, 2),
            "closing_credit": flt(acc.closing_credit, 2),
            "has_value": has_value,
            "is_group": acc.is_group
        }
        data.append(row)
    
    return data